This article deals with creating a SQL stored procedure for selecting a record.

In each of these stored procedure templates I am declaring a variety of documentation parameters in the header.

I've found these handy in the past when you are working in a team environment, or when you go back to a procedure at a later date. Its much easier to read a simple description in the header, than trawl through the SQL code looking for what it is doing.

So, this declares the procedure name, any parameters and return codes, and also details what it does, and who made it.

In a modified version of this I also hold the SVN revision number here.

The next block of code performs a select on the sysObjects table (part of the Master database). It is checking for the existence of itself. If it finds itself, it will drop the procedure. Note that throughout all of these scripts we are telling the user at each stage what is going on, by printing useful english output back to the screen.

By now we have identified wether or not the procedure previously existed, and if it did, we have dropped it, so we know that we are all good to go. So to create our Insert procedure, we print out a message to the user, then using the "CREATE PROCEDURE" command we create our procedure.

At this point you substitute the "@field" value with your field name, and the [datatype] and (datasize) with the correct values. As an example of this the first line in this proc will create a field named 'varName' with a data type of varchar with a character limit of 100. Just list your fields one after another, seperating with a comma.

After that we create the SQL code, as per usual. We have a Select statement, listing the fields, and the SQL variable declaration (@var). Then we check for any errors, and return a success message if it all worked ok!

By using a script like this I've found that its really simple to have a repeatable standard process that is easy to implement across a team of developers, ensuring you get the same results, no matter who writes the query. It is also very useful if you have a seperate implementation team, as these scripts are re-runnable, they clear up after themselves.

Download the full template here.

The Register recently posted an interesting article on the most 'popular' development languages in use today, and a brief dissection of why they have been nominated by the community.

Link here

Selecting the right code base to develop your applications in has always been a tricky decision. There are many criteria involved in selecting the right technology, and picking the wrong one can have far reaching consequences for the project, and indeed the application life in general. The article above points out many of the good, and bad points of a range of different languages, but I thought I would explore some of them in more depth.

I've grazed over several major topics here but will be fleshing out some of these points into full discussions, hopefully over the coming weeks.

One of the points that they touch on is resource availability. This is increasing becoming an issue in many development circles. With an increase in the number of available development languages, what with older languages remaining in production environments for longer, and many new languages entering the market this is only set to become even more prominent. How many languages must a developer know to become a useful project asset? How flexible is a development team? Is there really such a thing as a .Net developer, or a php developer anymore? In the last few years development staff have had to broaden their horizons substantially, just to stay in a team.

Other 'new' factors have risen to prominence, such as the Kudos factor. Is it cool to develop in a newly to market language? What are the problems associated with being an early adopter? Lack of resource springs to mind, and the development of new languages themselves can be very rocky indeed. I personally have been involved with, or used late beta or early version 1.X languages and frameworks, just to have them take a radically different approach on the next iteration, simply due to their immaturity. Also with the growth of some popular sites (google/facespace etc) they have also popularised the development languages they use. Just bear in mind that a project does not live or die by the code base being used.

Of course there is always the ever present split over open source or establishment development environments. This may often be dictated for a project due to wider issues. If your server architecture is unix, then you are unlikely to try and develop that new application in .Net. Both bring their own plus's and minus's I will go into more depth with those here.

One point that isn't mentioned the differing costs associated with many of these languages. Using open source software may dramatically reduce the costs of production, but could lead to higher staff costs, as experienced technical resources can demand a premium. Similarly using enterprise level vendor products such as .Net may well expand your resource pool due to its relative easy of use, but the costs of both server and client development software soon climbs.

One of the most overlooked aspects of deciding the technology base for your next application is the interconnectivity with our in-house, or third party systems. Are you exposing any elements as external services? Having worked with both confused.com, and gocompare.com I've had first-hand experience of the non-standardisation of WebServices. Both companies use differing technologies (VB and .Net) and interface with a WSDL service in totally different ways. For an open standard remote services are a mixed bag when dealing with different platforms. The last thing you want is to be writing translation applications for your actual application.

This article deals with creating a SQL stored procedure for inserting a record.

In each of these stored procedure templates I am declaring a variety of documentation parameters in the header.

I've found these handy in the past when you are working in a team environment, or when you go back to a procedure at a later date. Its much easier to read a simple description in the header, than trawl through the SQL code looking for what it is doing.

So, this declares the procedure name, any parameters and return codes, and also details what it does, and who made it.

In a modified version of this I also hold the SVN revision number here.

The next block of code performs a select on the sysObjects table (part of the Master database). It is checking for the existence of itself. If it finds itself, it will drop the procedure. Note that throughout all of these scripts we are telling the user at each stage what is going on, by printing useful english output back to the screen.

By now we have identified wether or not the procedure previously existed, and if it did, we have dropped it, so we know that we are all good to go. So to create our Insert procedure, we print out a message to the user, then using the "CREATE PROCEDURE" command we create our procedure.

At this point you substitute the "@field" value with your field name, and the [datatype] and (datasize) with the correct values. As an example of this the first line in this proc will create a field named 'varName' with a data type of varchar with a character limit of 100. Just list your fields one after another, seperating with a comma.

After that we create the SQL code, as per usual. We have an Insert statement, listing the fields, and the SQL variable declaration (@var). Then we check for any errors, and return a success message if it all worked ok!

By using a script like this I've found that its really simple to have a repeatable standard process that is easy to implement across a team of developers, ensuring you get the same results, no matter who writes the query. It is also very useful if you have a seperate implementation team, as these scripts are re-runnable, they clear up after themselves.

Download the full template here.